# 1.导包pymysql
import pymysql


class DBTools:

    # 类方法，可以用类名，直接调用类方法
    @classmethod
    # 私有方法，对用户不可见，只需要看到下面两个方法即可
    def __create_conn(cls):
        # 建立与数据库的连接
        # conn = pymysql.connect(host='192.168.174.134',
        #                             port=3306,
        #                             user='root',
        #                             password='123456',
        #                             database='test_db',
        #                             charset='utf8')
        conn = pymysql.connect(host='192.168.174.132',
                               port=3306,
                               user='root',
                               password='iTest_2022_mysql',
                               database='ihrm',
                               charset='utf8')
        return conn

    # 执行查询SQL
    @classmethod
    def query_one(cls, sql):
        conn = None
        cursor = None
        res = None

        try:
            # 2.建立与数据库的连接
            conn = DBTools.__create_conn()

            # 3.用连接，创建游标
            cursor = conn.cursor()

            # 4.用游标，execute执行sql
            cursor.execute(sql)

            # 5.提取数据
            res = cursor.fetchone()

        except Exception as err:
            # 打印异常信息
            print("执行 查询 SQL 失败：", err)
        finally:
            # 6.关闭游标cursor
            cursor.close()
            # 7.关闭连接conn
            conn.close()

            return res

    # 执行增删改SQL
    @classmethod
    def dml_sql(cls, sql):
        conn = None
        cursor = None
        try:
            # 2.建立与数据库的连接
            conn = DBTools.__create_conn()

            # 3.用连接，创建游标
            cursor = conn.cursor()

            # 4.用游标，execute执行sql
            cursor.execute(sql)

            # 5.提交事务
            conn.commit()

            # 5.1返回影响的行数
            return conn.affected_rows()
        except Exception as err:
            # 打印异常信息
            print("执行 增删改 SQL 失败：", err)

            # 5.回滚事务
            conn.rollback()
        finally:
            # 6.关闭游标cursor
            cursor.close()
            # 7.关闭连接conn
            conn.close()


if __name__ == '__main__':
    # 调用查询
    res = DBTools.query_one('select * from t_book;')
    print(res)
    # (1, '射雕英雄传', datetime.date(1980, 5, 1), 12, 34, 0)

    # 调用dml
    res = DBTools.dml_sql("update t_book set `read` = `read` +1 where title = '西游记';")
    print(res)
    # 1
    # (1054, "Unknown column 'titl' in 'where clause'")
    # None
